lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-09-12(胡工).sql


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_OrdersPerson')
BEGIN
	DROP VIEW [dbo].Vw_StaffPerformance_OrdersPerson
END 
GO
create View Vw_StaffPerformance_OrdersPerson
as
SELECT 
ID
,Pay_OrdNumber as 订单号
,Pay_ShootingName as 拍摄阶段
,Pay_Category as 收款类别
,Pay_TwoPinsCategory  as 二销类别编号
,dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory) AS 二销类别名称
,Pay_AmountOf as 收款金额
,Pay_OpenSingle as  接单人编号
, dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS 接单人名称
,Pay_ThePayee as 收款人编号
,dbo.fn_CheckUserIDGetUserName(Pay_ThePayee) AS 收款人名称
,Pay_PaymentMethod as 付款方式编号
,dbo.fn_GetClassCodeToName(Pay_PaymentMethod, Pay_PaymentMethod) AS 付款方式名称
,Pay_OrdersLocation as 接单地点 
,Pay_ReceivableProject as 收款项目
,Pay_FinancialAudit as 审核状态
,Pay_FinancialAuditdPeople as 审核人
,Pay_Remark as 备注
,Pay_CreateDatetime as 收款时间
,Pay_Type as 收款类型 
,(case Pay_Type
       when 0 then (select Cus_Name from tempTB_AggregationCustomer where Pay_OrdNumber=GP_OrderNumber)
	   when 1 then (select Tsorder_CustomerName from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number)
	   when 2 then (select Cus_Name from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number)  else '' end) as '客户名称'
,(case Pay_Type
		when 0 then (select Ord_PhotographyCategory from tb_ErpOrder where Pay_OrdNumber=Ord_Number)else '' end) as '套系类别'
,(case Pay_Type
		when 0 then (select Ord_SeriesName from tb_ErpOrder where Pay_OrdNumber=Ord_Number)else '' end) as '套系名称'
,(case Pay_Type
		when 0 then (select Ord_OrderClass from tb_ErpOrder where Pay_OrdNumber=Ord_Number)else '' end) as '订单类别'
,(case Pay_Type
		when 0 then (select Ord_SeriesPrice from tb_ErpOrder where Pay_OrdNumber=Ord_Number)
		when 1 then (select Tsorder_Money from Vw_TwoSalesOrder where Pay_OrdNumber=Tsorder_Number)
	    when 2 then (select Dsro_Amount from View_DressSaleRentalOrder where Pay_OrdNumber=Dsro_Number) end) as '应收金额'
,(case Pay_Type
		when 0 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) 
		when 1 then (dbo.fn_GetClassCodeToName(Pay_TwoPinsCategory, Pay_TwoPinsCategory)) 
	    when 2 then Pay_ReceivableProject end) as '项目名称'
,case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') end as '主门市比重'
,case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') end as '副门市比重'
, (  case LEFT(Pay_OpenSingle,charindex(',',Pay_OpenSingle,1)) 
         when '' then Pay_OpenSingle
		 else  LEFT(Pay_OpenSingle,charindex(',',Pay_OpenSingle,1)-1)  end)as '主门市'
,len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))) as '副门市个数'
--, (  case Pay_Category
--         when '后期收款' then ( len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))
--		 else (select count(*) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Pay_OrdNumber  and OrdPe_Type='1') end) as '副门市个数'

,(case (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))
		 when 0 then Pay_AmountOf
		 else  cast(Pay_AmountOf * case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') end*0.01  as   numeric(9,2)) 
 end) as '主门市金额'
,(case (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))
         when 0 then 0
		 else  cast(Pay_AmountOf * case when (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') is null then '0' else (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') end*0.01/(len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))  as   numeric(9,2)) 
 end) as '副门市金额'
 ,(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) as 副订单号
,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) 
   when 1 then (select Ordpg_PhotographyTime from tb_ErpOrdersPhotography where Ordpg_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) ) 
   when 2 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) 
   else '' end) as 最后拍摄时间
 ,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordpg_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber)  and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select Ordv_FilmSelectionStatus from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   when 1 then (select  Ordv_FilmSelectionStatus from tb_ErpOrderDigital where  Ordv_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber))
   when 2 then (select Ordv_FilmSelectionStatus from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   else '' end) as 选片状态
 ,(case (select Ord_Type from tb_ErpOrder where Ord_Number=Pay_OrdNumber)
   when 0 then (select Ordv_FilmSelectionTime from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   when 1 then (select  Ordv_FilmSelectionTime from tb_ErpOrderDigital where  Ordv_ViceNumber=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber))
   when 2 then (select Ordv_FilmSelectionTime from tb_ErpOrderDigital where Ordv_Number=Pay_OrdNumber)
   else '' end) as 选片时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=(select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = (select top 1 Ordpg_ViceNumber from tb_ErpOrdersPhotography where Ordpg_Number=Pay_OrdNumber) and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
FROM tb_ErpPayment
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_StaffPerformance_Photograph')
BEGIN
	DROP VIEW [dbo].Vw_StaffPerformance_Photograph
END 
GO
create View Vw_StaffPerformance_Photograph
as
SELECT 
Ordv_Number as 主订单
,Ordv_ViceNumber as 副订单
,(select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number) as 订单类型
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 拍摄名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) 
   when 1 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber) 
   when 2 then (select max(Ordpg_PhotographyTime) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number) 
   else '' end) as 最后拍摄时间
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 1 then (select count(id) as id from tb_ErpOrdersPhotography where  Ordv_ViceNumber=Ordpg_ViceNumber and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   when 2 then (select count(id) as id from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and (Ordpg_PhotographyStatus=0 or Ordpg_PhotographyStatus=2))
   else '' end) as 未拍个数
,(case Ordv_FilmSelectionStatus when 1 then 'OK'  else '未选' end) as 选片状态
,Ordv_FilmSelectionTime as 选片时间
,(case when (select Count(*) from (select [OPlist_PickupStatus] from [tb_ErpOrderProductList] where [OPlist_ViceNumber]=Ordv_ViceNumber and OPlist_Type = '2' and [OPlist_PickupStatus] = '0') as ta)>0  then '未取' else 'OK' end) AS 取件状态
,(select top 1 OPlist_PickupTime from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = '2' and OPlist_PickupTime is not null order by OPlist_PickupTime) AS 取件日期
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADDEBFJDFFC')
   when 1 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_ViceNumber=Ordv_ViceNumber and Ordpg_SightsLevel='BEBBBCADDEBFJDFFC')
   when 2 then  (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADDEBFJDFFC')
   else '' end) as '景点一级个数'
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADEGBGAFFJC')
   when 1 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_ViceNumber=Ordv_ViceNumber and Ordpg_SightsLevel='BEBBBCADEGBGAFFJC')
   when 2 then  (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADEGBGAFFJC')
   else '' end) as '景点二级个数'
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADAFBHBCHCI')
   when 1 then (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_ViceNumber=Ordv_ViceNumber and Ordpg_SightsLevel='BEBBBCADAFBHBCHCI')
   when 2 then  (select count(*) as [count] from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number and Ordpg_SightsLevel='BEBBBCADAFBHBCHCI')
   else '' end) as '景点三级个数'
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_Photographer from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主摄影师ID
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_Photographer) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主摄影师名称
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_PhotographyAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 摄影助理ID
 ,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_PhotographyAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 摄影助理名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_MakeupArtist from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主化妆ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupArtist) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 主化妆名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_MakeupAssistant from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 化妆助理ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_MakeupAssistant) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 化妆助理名称
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ Ordpg_BootDivision from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 引导师ID
,(case (select Ord_Type from tb_ErpOrder where Ordv_Number=Ord_Number)
   when 0 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   when 1 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordv_ViceNumber=Ordpg_ViceNumber for xml path('')),1,1,''))
   when 2 then (select stuff((select ','+ dbo.fn_CheckUserIDGetUserName(Ordpg_BootDivision) from tb_ErpOrdersPhotography where Ordpg_Number=Ordv_Number for xml path('')),1,1,''))
   else '' end) as 引导师名称
,Ordv_EarlyRepairName as '初修师ID'
,dbo.fn_CheckUserIDGetUserName(Ordv_EarlyRepairName) as '初修师'
,Ordv_RefinementName as '精修师ID'
,dbo.fn_CheckUserIDGetUserName(Ordv_RefinementName) as '精修师'
,Ordv_DesignerName as '设计师ID'
,dbo.fn_CheckUserIDGetUserName(Ordv_DesignerName) as '设计师'
,Vw_StaffPerformance_OrdersPerson.ID
,订单号
,拍摄阶段
,收款类别
,二销类别编号
,二销类别名称
,收款金额
,接单人编号
,接单人名称
,收款人编号
,收款人名称
,付款方式编号
,付款方式名称
,接单地点
,收款项目
,审核状态
,审核人
,备注
,收款时间
,收款类型
,客户名称
,套系类别
,套系名称
,订单类别
,应收金额
,项目名称
FROM tb_ErpOrderDigital 
left join Vw_StaffPerformance_OrdersPerson on Ordv_Number=订单号
where 订单号 is not null
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetProductListPreSaleQuantity]') and xtype in (N'FN', N'IF', N'TF'))
BEGIN
	DROP FUNCTION [dbo].fn_GetProductListPreSaleQuantity
END 
GO
--订单商品表
create  function [dbo].[fn_GetProductListPreSaleQuantity](@Prod_Number varchar(800))
Returns varchar(800)
As
Begin
	Declare @ProdCount int
	set @ProdCount = (select sum(OPlist_ProdQuantity) from tb_ErpOrderProductList where OPlist_ProdNumber = @Prod_Number and OPlist_Type = '2' )
	return @ProdCount
End	
GO